﻿Imports System.Data
Imports System.Data.SqlClient
Public Class LotacaoDAL
    Public Sub New()
        'contrutor padrão
    End Sub
    Dim cmd As SqlCommand
    Dim objD As Dados
#Region "Métodos"
    Public Function inserirLotacao(ByVal vei_codigo As Integer, ByVal data_lot As Date, ByVal data_part As Date, ByVal lot_status As String) As Integer
        Dim codigo As Integer
        Try
            cmd = New SqlCommand()
            objD = New Dados()
            cmd.CommandText = "insert into lotacao(vei_codigo, lot_data, lot_partida) values " & _
                "(@vei_codigo, @lot_data, @lot_partida)"
            cmd.Parameters.Add(New SqlParameter("@vei_codigo", SqlDbType.Int)).Value = vei_codigo
            cmd.Parameters.Add(New SqlParameter("@lot_data", SqlDbType.Date)).Value = data_lot
            cmd.Parameters.Add(New SqlParameter("@lot_partida", SqlDbType.Date)).Value = data_part
            cmd.Parameters.Add(New SqlParameter("@lot_status", SqlDbType.VarChar)).Value = lot_status
            codigo = objD.executacomandoAI(cmd, "lotacao")

        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return codigo
    End Function

    Public Sub alterarLotacao(ByVal lot_codigo As Integer, ByVal data_lot As Date, ByVal data_part As Date, ByVal lot_status As String)
        Try
            cmd = New SqlCommand()
            objD = New Dados()
            cmd.CommandText = "update lotacao set " & _
                "lot_data = @lot_data, lot_partida = @lot_partida, lot_status = @lot_status where lot_codigo = @lot_codigo"
            cmd.Parameters.Add(New SqlParameter("@lot_data", SqlDbType.Date)).Value = data_lot
            cmd.Parameters.Add(New SqlParameter("@lot_partida", SqlDbType.Date)).Value = data_part
            cmd.Parameters.Add(New SqlParameter("@lot_codigo", SqlDbType.Int)).Value = lot_codigo
            cmd.Parameters.Add(New SqlParameter("@lot_status", SqlDbType.VarChar)).Value = lot_status
            objD.executacomando(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
    End Sub

    Public Sub excluirLotacao(ByVal lot_codigo As Integer)
        Try
            objD = New Dados()

            cmd = New SqlCommand()
            cmd.CommandText = "update entregas set ent_status = 'inativada' where lot_codigo = @lot_codigo"
            cmd.Parameters.Add(New SqlParameter("@lot_codigo", SqlDbType.Int)).Value = lot_codigo
            objD.executacomando(cmd)


            cmd = New SqlCommand()
            objD = New Dados()
            cmd.CommandText = "delete from lotacao where lot_codigo = @lot_codigo"
            cmd.Parameters.Add(New SqlParameter("@lot_codigo", SqlDbType.Int)).Value = lot_codigo
            objD.executacomando(cmd)


            cmd = New SqlCommand()
            cmd.CommandText = "update cargas set car_status = 1 where car_codigo in (select car_codigo from cargas_da_lotacao where lot_codigo = @lot_codigo) "
            cmd.Parameters.Add(New SqlParameter("@lot_codigo", SqlDbType.Int)).Value = lot_codigo

            objD.executacomando(cmd)


            cmd = New SqlCommand()
            cmd.CommandText = "delete from cargas_da_lotacao where lot_codigo = @lot_codigo"
            cmd.Parameters.Add(New SqlParameter("@lot_codigo", SqlDbType.Int)).Value = lot_codigo
            objD.executacomando(cmd)


        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
    End Sub


    Public Sub inserirCargas(ByVal lot_codigo As Integer, ByVal car_codigo As Integer, ByVal age_codigo As Integer)
        Try
            objD = New Dados()
            cmd = New SqlCommand()
            cmd.CommandText = "exec sp_Gravar_Carregamento @lot_codigo, @car_codigo, @age_codigo"
            cmd.Parameters.Add(New SqlParameter("@lot_codigo", SqlDbType.Int)).Value = lot_codigo
            cmd.Parameters.Add(New SqlParameter("@car_codigo", SqlDbType.Int)).Value = car_codigo
            cmd.Parameters.Add(New SqlParameter("@age_codigo", SqlDbType.Int)).Value = age_codigo
            objD.executacomando(cmd)



        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
    End Sub


    Public Sub alterarCargas(ByVal lot_codigo As Integer, ByVal car_codigo As Integer, ByVal age_codigo As Integer)
        Try

            Dim verif As Boolean

            verif = verificaStatus(lot_codigo)

            If verif = True Then

                objD = New Dados()


                cmd = New SqlCommand()
                cmd.CommandText = "update cargas set car_status = 1 where car_codigo in (select car_codigo from cargas_da_lotacao where lot_codigo = @lot_codigo) "
                cmd.Parameters.Add(New SqlParameter("@lot_codigo", SqlDbType.Int)).Value = lot_codigo
                objD.executacomando(cmd)


                cmd = New SqlCommand()
                cmd.CommandText = "delete from cargas_da_lotacao where lot_codigo = @lot_codigo"
                cmd.Parameters.Add(New SqlParameter("@lot_codigo", SqlDbType.Int)).Value = lot_codigo
                objD.executacomando(cmd)


                cmd = New SqlCommand()
                cmd.CommandText = "exec sp_Gravar_Carregamento @lot_codigo, @car_codigo, @age_codigo"
                cmd.Parameters.Add(New SqlParameter("@lot_codigo", SqlDbType.Int)).Value = lot_codigo
                cmd.Parameters.Add(New SqlParameter("@car_codigo", SqlDbType.Int)).Value = car_codigo
                cmd.Parameters.Add(New SqlParameter("@age_codigo", SqlDbType.Int)).Value = age_codigo
                objD.executacomando(cmd)

            Else
                Throw New Exception("Apenas podem ser alteradas cargas de lotações em aberto.")

            End If


        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
    End Sub

    Public Function carregarveiculo(ByVal vei_codigo As Integer) As DataTable
        Try
            Dim tab As New DataTable()

            cmd = New SqlCommand()
            objD = New Dados()
            cmd.CommandText = "exec sp_CarregarVeiculo @vei_codigo"
            cmd.Parameters.Add(New SqlParameter("@vei_codigo", SqlDbType.Int)).Value = vei_codigo
            tab = objD.executaConsulta(cmd)

            Return tab
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
    End Function

    Public Sub excluirCarga(ByVal car_codigo As Integer, ByVal lot_codigo As Integer)
        Try
            cmd = New SqlCommand()
            objD = New Dados()
            cmd.CommandText = "delete from cargas_da_lotacao where car_codigo = @car_codigo and lot_codigo = @lot_codigo"
            cmd.Parameters.Add(New SqlParameter("@car_codigo", SqlDbType.Int)).Value = car_codigo
            cmd.Parameters.Add(New SqlParameter("@lot_codigo", SqlDbType.Int)).Value = lot_codigo
            objD.executacomando(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
    End Sub

    Public Function localizar(ByVal lot_codigo As Integer) As DataTable
        Dim tab As DataTable
        Try
            cmd = New SqlCommand()
            objD = New Dados()
            cmd.CommandText = "select * from lotacao where lot_codigo = @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = lot_codigo
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function

    Public Function localizar(ByVal placa As String, ByVal inicioCar As Date, ByVal fimCar As Date, ByVal inicioPrev As Date, ByVal fimPrev As Date, ByVal status As String) As DataTable
        Dim tab As DataTable
        Try
            Dim sql As String
            cmd = New SqlCommand()
            objD = New Dados()
            sql = "select * from vw_lotacao where "
            If placa <> "" Then
                sql += "vei_placa like @placa and "
            End If
            If (inicioCar <> Date.Parse("1111-11-11")) And (fimCar <> Date.Parse("1111-11-11")) Then
                sql += "lot_data between @inicioCar and @fimCar and "
            End If
            If (inicioPrev <> Date.Parse("1111-11-11")) And (fimPrev <> Date.Parse("1111-11-11")) Then
                sql += "lot_partida between @inicioPrev and @fimPrev and "
            End If
            If status <> "AF" Then
                If status <> "" Then
                    sql += "lot_status like @status and "
                Else
                    sql += "lot_status like '' and "
                End If
            End If
            sql += "vei_codigo > 0"
            cmd.CommandText = sql
            cmd.Parameters.Add(New SqlParameter("@placa", SqlDbType.VarChar)).Value = placa
            cmd.Parameters.Add(New SqlParameter("@inicioCar", SqlDbType.Date)).Value = inicioCar
            cmd.Parameters.Add(New SqlParameter("@fimCar", SqlDbType.Date)).Value = fimCar
            cmd.Parameters.Add(New SqlParameter("@inicioPrev", SqlDbType.Date)).Value = inicioPrev
            cmd.Parameters.Add(New SqlParameter("@fimPrev", SqlDbType.Date)).Value = fimPrev
            cmd.Parameters.Add(New SqlParameter("@status", SqlDbType.VarChar)).Value = status
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function

    Public Function localizarCargas(ByVal lot_codigo As Integer) As DataTable
        Dim tab As DataTable
        Try
            cmd = New SqlCommand()
            objD = New Dados()
            cmd.CommandText = "select * from vw_cargas_lotacao where lot_codigo = @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = lot_codigo
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function

    Function localizar(ByVal descricao As String) As DataTable
        Dim tab As DataTable = Nothing
        Try
            cmd = New SqlCommand
            objD = New Dados
            If (descricao = "ultimo") Then
                cmd.CommandText = "SELECT lot_codigo = max(lot_codigo) FROM lotacao "
            ElseIf (descricao = "primeiro") Then
                cmd.CommandText = "SELECT lot_codigo = min(lot_codigo) FROM lotacao "
            End If
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
        tab = Nothing
    End Function

    Function localizarProxAnterior(ByVal descricao As String, ByVal codigo As Integer) As DataTable
        Dim tab As DataTable = Nothing
        Try
            cmd = New SqlCommand
            objD = New Dados
            If (descricao = "proximo") Then
                cmd.CommandText = "SELECT lot_codigo FROM lotacao WHERE lot_codigo = (SELECT MIN(lot_codigo) FROM " +
                                    "lotacao WHERE lot_codigo > @codigo)"
            ElseIf (descricao = "anterior") Then
                cmd.CommandText = "SELECT lot_codigo FROM lotacao  WHERE lot_codigo = (SELECT MAX(lot_codigo) FROM " +
                                    "lotacao WHERE lot_codigo < @codigo)"
            End If
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
        tab = Nothing
    End Function

    Public Function localizarCargasDisponiveis(ByVal descricao As String) As DataTable
        Dim tab As DataTable
        Try
            cmd = New SqlCommand()
            objD = New Dados()
            cmd.CommandText = "SELECT cda.car_codigo, car.car_protocolo, car.car_peso, ac.age_codigo, (select top 1 cag.pes_codigo from clientes_do_agendamento cag where cag.age_codigo = ac.age_codigo and pes_tipo_dr like 'R') as age_remetente, (select top 1 cag.pes_codigo from clientes_do_agendamento cag where cag.age_codigo = ac.age_codigo and pes_tipo_dr like 'D') as age_destinatario, ac.age_data, ac.age_data_previsao, ro.rot_codigo, ro.rot_distancia " & _
                             " from cargas_do_agendamento cda inner join " & _
                             " agendamento_carga ac on ac.age_codigo = cda.age_codigo inner join " & _
                             " cargas car on car.car_codigo = cda.car_codigo inner join " & _
                             " pessoa pes on pes.pes_codigo in (select top 1 cag.pes_codigo from clientes_do_agendamento cag where cag.age_codigo = ac.age_codigo and pes_tipo_dr like 'D') inner join " & _
                             " rotas ro on ro.cid_destino = pes.cod_cidade " & _
                             " where car.car_status = 1 And ro.rot_ativa = 1 and car.car_protocolo like @descricao" & _
                             " order by age_data, ro.rot_distancia "
            cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" + descricao + "%"

            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function

    Public Function localizarDadosCarga(ByVal car_codigo As Integer) As DataTable
        Dim tab As DataTable
        Try
            cmd = New SqlCommand()
            objD = New Dados()
            cmd.CommandText = "select TOP 1 * from vw_dados_carga_lotacao where car_codigo = @car_codigo"
            cmd.Parameters.Add(New SqlParameter("@car_codigo", SqlDbType.Int)).Value = car_codigo
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function

    'Public Function consultaveiculosdisponiveis() As DataTable
    '    Dim tab As DataTable
    '    Try
    '        cmd = New SqlCommand()
    '        objD = New Dados()
    '        cmd.CommandText = "select * from veiculos"
    '        tab = objD.executaConsulta(cmd)
    '    Catch ex As Exception
    '        Throw ex
    '    End Try
    '    cmd = Nothing
    '    objD = Nothing
    '    Return tab
    'End Function

    Public Function verificaStatus(ByVal lot_codigo) As Boolean
        Dim tab As DataTable
        Try
            cmd = New SqlCommand()
            objD = New Dados()
            cmd.CommandText = "select lot_status from lotacao where lot_codigo = @lot_codigo and lot_status = 'Aberto'"
            cmd.Parameters.Add(New SqlParameter("@lot_codigo", SqlDbType.Int)).Value = lot_codigo
            tab = objD.executaConsulta(cmd)

            If tab.Rows.Count <> 0 Then
                Return True
            Else : Return False
            End If

        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
    End Function

#End Region
End Class
